/**
 * Copyright (c) 2016-2019 人人开源 All rights reserved.
 *
 * https://www.renren.io
 *
 * 版权所有，侵权必究！
 */

package cn.xorange.commons.utils.filter;


import cn.hutool.core.exceptions.UtilException;
import cn.xorange.commons.utils.exception.UtilsException;
import org.apache.commons.lang3.StringUtils;

/**
 * SQL过滤
 *
 * @author Mark sunlightcs@gmail.com
 */
public class SQLFilter {

    /**
     * 定义常用的 sql关键字
     */
    public static String[] SQL_REGEX = {"and ","extractvalue","updatexml","exec ","insert ","select ","delete ","update ","drop ","count ","chr ","mid ","master ","truncate ","char ","declare ","or ","+","user()"};

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 限制orderBy最大长度
     */
    private static final int ORDER_BY_MAX_LENGTH = 500;


    /**
     * SQL注入过滤, 关键字检查
     * @param str  待验证的字符串
     */
    public static String sqlInject(String str){
        if(StringUtils.isBlank(str)){
            return null;
        }
        //去掉'|"|;|\字符
        str = StringUtils.replace(str, "'", "");
        str = StringUtils.replace(str, "\"", "");
        str = StringUtils.replace(str, ";", "");
        str = StringUtils.replace(str, "\\", "");

        //转换成小写
        str = str.toLowerCase();

        //判断是否包含非法字符
        for(String keyword : SQL_REGEX){
            if(str.contains(keyword)){
                throw UtilsException.systemException("包含非法字符");
            }
        }

        return str;
    }

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value)
    {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value))
        {
            throw UtilsException.systemException("参数不符合规范，不能进行查询");
        }
        if (StringUtils.length(value) > ORDER_BY_MAX_LENGTH)
        {
            throw UtilsException.systemException("参数已超过最大限制，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value)
    {
        return value.matches(SQL_PATTERN);
    }
}
